import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;

import AllegroWebApi.AttribStruct;
import AllegroWebApi.CatInfoType;
import AllegroWebApi.CountryInfoType;
import AllegroWebApi.SearchResponseType;
import AllegroWebApi.SellFormType;
import AllegroWebApi.ShipmentDataStruct;
import AllegroWebApi.StateInfoStruct;
import AllegroWebApi.holders.ArrayOfCatsHolder;
import AllegroWebApi.holders.ArrayOfSearchResponseHolder;
import AllegroWebApi.holders.ArrayOfShipmentDataStructHolder;

import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;
import common.ConnectionDAO;



public class AllegroDAO {
	Connection con;
	PreparedStatement ps;
	Statement st;
	ResultSet rs;
	
    String insertOffer =" INSERT INTO offer  ( "+
    		" id , "+
    		 " category_id , "+
    		 " title , "+
    		 " price , "+
    		 " data_stop , "+
    		 " city , "+
    		 " state_id , "+
    		 " country_id , "+
    		 " promotion , "+
    		 " pic_url , "+
    		 " price_deliv , "+
    		 " free_deliv , "+
    		 " time_deliv , "+
    		 " user_id , "+
    		 " user_name ) " +
    	" VALUES (?, "+
    		 " ? , "+
    		 " ? , "+ 
    		" ? , "+
    		 " ? , "+
    		 " ? , "+
    		 " ? , "+
    		 " ? , "+
    		 " ? , "+
    		 " ? , "+
    		 " ? , "+
    		 " ? , "+
    		 " ? , "+
    		 " ? , "+
    		 " ?  );";
    
    String atrOfferSql = "insert into sell_attribute_value(offer_id , sell_attribute_id , name , `value`) values( ? , ? , ? , ? );";
    
    public void addOffers(ArrayOfSearchResponseHolder o , Integer categoryId){
    	try {
    		HashMap<String ,Integer> paramMap =new HashMap<String, Integer>();
    		if(o.value.length==0)
    			return;
    		
    		String atrSql = "select csa.sell_attribute_id , sa.title from category_sell_attribute csa " +
    				"join sell_attribute sa ON csa.sell_attribute_id = sa.id where csa.category_id = ?;";
    		PreparedStatement pr = con.prepareStatement(insertOffer);
    		PreparedStatement atrPs = con.prepareStatement(atrSql);
    		PreparedStatement atrIns = con.prepareStatement(atrOfferSql);
    		
    		atrPs.setInt(1, categoryId);
    		ResultSet atrRs = atrPs.executeQuery();
    		
    		String atrName; Integer atrId;
    		//ArrayList<AtrData> atrList = new  ArrayList<AllegroDAO.AtrData>();
    		while(atrRs.next()){ // sprawdzenie czy atrybuty s� zgodne
    			atrId = atrRs.getInt(1);
    			atrName = atrRs.getString(2);
    			//atrList.add(new AtrData(atrId , atrName));
    			paramMap.put(atrName.trim(), atrId);
    		}
    		SearchResponseType d;
    		AttribStruct s;
    		//dodawanie ofert
    		for(int i = 0 ; i < o.value.length; i++){
    			d = o.value[i];
    			int atrNr = 0 ;
    			for(int aa = 0 ; aa < o.value[i].getSItAttribsList().length ; aa++){ // sprawdzenie czy atr. sie zgadzaja
    					if(!paramMap.containsKey(o.value[i].getSItAttribsList()[aa].getAttribName().trim())){
    					Integer id = insertNewAttr(categoryId , o.value[i].getSItAttribsList()[aa].getAttribName());
    					paramMap.put(o.value[i].getSItAttribsList()[aa].getAttribName().trim(), id);
    					}
				}
    		
    			for( int k = 0 ; k < d.getSItAttribsList().length ; k++){
    			s = d.getSItAttribsList()[k];
    				for(int h = 0 ; h < s.getAttribValues().length ; h ++){
    					atrIns.setLong(1, d.getSItId());
    	    			atrIns.setInt(2,paramMap.get(s.getAttribName().trim()));
    	    			atrIns.setString(3, s.getAttribName().trim());
    	    			atrIns.setString(4, s.getAttribValues()[h]);
    	    			atrIns.addBatch();
    				}
    			}
    			atrIns.executeBatch();
    			
				// set attribute
    			int pos = 1;
    			
    			pr.setLong(pos++, d.getSItId());
    			pr.setInt(pos++, categoryId);
    			pr.setString(pos++, d.getSItName());
    			pr.setFloat(pos++, d.getSItBuyNowPrice());
    			pr.setDate(pos++, new Date( d.getSItEndingTime()));
    			pr.setString(pos++, d.getSItCity());
    			pr.setInt(pos++, d.getSItState());
    			pr.setInt(pos++, d.getSItCountry());
    			pr.setInt(pos++, d.getSItFeatured());
    			pr.setString(pos++, d.getSItThumbUrl());
    			pr.setFloat(pos++, d.getSItPostage());
    			pr.setInt(pos++, d.getSItHasFreeShipping());
    			pr.setInt(pos++, d.getSItOrderFulfillmentTime());
    			pr.setInt(pos++, d.getSItSellerInfo().getSellerId());
    			pr.setString(pos++, d.getSItSellerInfo().getSellerName());
    			//
    			pr.addBatch();
    			System.out.println("--- id : " + d.getSItId()  + " : " + d.getSItName());
    		}
    		
    		pr.executeBatch();
    		
    		
    	/*	boolean zg;
    		AttribStruct[] atrTmp =  o.value[0].getSItAttribsList();
    		for( int i = 0 ; i < atrTmp.length ; i++){
	    			zg = false;
	    			for(AtrData data: atrList){
	    				if(data.name.trim().equals(atrTmp[i].getAttribName().trim())){
	    					System.out.println("zgodno�� parametr�w." + " db id : " );
	    					zg = true;
	    				}
	    			
	    			
	    			if(zg == false){
	    				System.out.println("niezgodnosc : " + data.name);
	    				for( int i = 0 ; i < atrTmp.length ; i++){
	    					System.out.println(" param name : " + atrTmp[i].getAttribName());
	    				}
	    			}
	    		}
    		}
	           PreparedStatement pr = con.prepareStatement(insertOffer);
	           
	           for(SearchResponseType data : o.value){
	        	  // pr.addBatch();
	        	 
	           }
	         */

	        } catch (SQLException ex) {
	            Logger lgr = Logger.getLogger(AllegroDAO.class.getName());
	            lgr.log(Level.SEVERE, ex.getMessage(), ex);

	        } finally {
	            try {
	                if (rs != null) {
	                    rs.close();
	                }
	                if (st != null) {
	                    st.close();
	                }
	               

	            } catch (SQLException ex) {
	                Logger lgr = Logger.getLogger(AllegroDAO.class.getName());
	                lgr.log(Level.WARNING, ex.getMessage(), ex);
	            }
	        }
    }
    
    private Integer insertNewAttr(Integer categoryId, String attribName) {
    	Integer id =null;
    	try{
	    	   PreparedStatement pr = con.prepareStatement("insert into sell_attribute(form_id, title , `values`, values_desc) " +"values( 0 , ?, '', '');");
	    	   PreparedStatement pr2 = con.prepareStatement("insert into category_sell_attribute(id ,  category_id, sell_attribute_id) " +"values( null , ?, ?);");
	           
	        	pr.setString(1, attribName);
	        	pr.executeUpdate();
	        	ResultSet key = pr.getGeneratedKeys();
	        	if(key.next())
	        		id = key.getInt(1);
	        	if(id == null)
	        		return null;
	        	
	        	pr2.setInt(1, categoryId);
	        	pr2.setInt(2, id);
	        	pr2.executeUpdate();
	        	System.out.println("+++++===+++ " + "dodano nowy param : " + attribName + " cat id : " +categoryId);
	        	
	        } catch (SQLException ex) {
	            Logger lgr = Logger.getLogger(AllegroDAO.class.getName());
	            lgr.log(Level.SEVERE, ex.getMessage(), ex);
	        } catch(Exception e){
	        } finally {
	            try {
	                if (rs != null) {
	                    rs.close();
	                }
	                if (st != null) {
	                    st.close();
	                }
	            } catch (SQLException ex) {
	                Logger lgr = Logger.getLogger(AllegroDAO.class.getName());
	                lgr.log(Level.WARNING, ex.getMessage(), ex);
	            }
	        }
		return id;
	}

	class AtrData{
    	String name;
    	Integer id;
    	AtrData(Integer id , String n){
    		this.id=id; this.name=n;
    	}
    }
    
    public void addCategory(ArrayOfCatsHolder list){
    	 try {
	           PreparedStatement pr = con.prepareStatement("insert into category(id , parent_id, name) " +	"values( ? , ? , ?);");
	           
	           for(CatInfoType data : list.value){
	        	   pr.setInt(1, data.getCatId());
	        	   pr.setInt(2, data.getCatParent());
	        	   pr.setString(3, data.getCatName());
	        	   
	        	   if(pr.executeUpdate()>0)
	        	   System.out.println("added : " + data.getCatName());
	           }
	         

	        } catch (SQLException ex) {
	            Logger lgr = Logger.getLogger(AllegroDAO.class.getName());
	            lgr.log(Level.SEVERE, ex.getMessage(), ex);

	        } finally {
	            try {
	                if (rs != null) {
	                    rs.close();
	                }
	                if (st != null) {
	                    st.close();
	                }
	               

	            } catch (SQLException ex) {
	                Logger lgr = Logger.getLogger(AllegroDAO.class.getName());
	                lgr.log(Level.WARNING, ex.getMessage(), ex);
	            }
	        }
    }
    
    public void addCountry(CountryInfoType[] co){
    	try{
    	   PreparedStatement pr = con.prepareStatement("insert into country(id ,  name) " +	"values( ? , ?);");
           
           for(CountryInfoType data : co){
        	 pr.setInt(1, data.getCountryId());
        	 pr.setString(2, data.getCountryName());
        	   
        	   if(pr.executeUpdate()>0)
        	   System.out.println("added : " + data.getCountryName());
           }
         

        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(AllegroDAO.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);

        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
               

            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(AllegroDAO.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }

    
	public AllegroDAO(){
		con = ConnectionDAO.getConnection();
	}

	public void addState(StateInfoStruct[] state, int countryId) {
		try{
	    	   PreparedStatement pr = con.prepareStatement("insert into state(id ,  country_id, name) " +	"values( ? , ?, ?);");
	           
	           for(StateInfoStruct data : state){
	        	pr.setInt(1, data.getStateId());
	        	pr.setInt(2, countryId);
	        	pr.setString(3, data.getStateName());
	        	   
	        	   if(pr.executeUpdate()>0)
	        		   System.out.println("id : " + data.getStateId() + " name : " + data.getStateName());
	           }
	         

	        } catch (SQLException ex) {
	            Logger lgr = Logger.getLogger(AllegroDAO.class.getName());
	            lgr.log(Level.SEVERE, ex.getMessage(), ex);

	        } finally {
	            try {
	                if (rs != null) {
	                    rs.close();
	                }
	                if (st != null) {
	                    st.close();
	                }
	               

	            } catch (SQLException ex) {
	                Logger lgr = Logger.getLogger(AllegroDAO.class.getName());
	                lgr.log(Level.WARNING, ex.getMessage(), ex);
	            }
	        }
		
	}

	public void addShipment(ArrayOfShipmentDataStructHolder ship , int countryId) {
		try{
	    	   PreparedStatement pr = con.prepareStatement("insert into shipment(id ,  country_id, name, type) " +	"values( ? , ?, ?, ?);");
	           
	        for(ShipmentDataStruct t : ship.value){
	        	pr.setInt(1, t.getShipmentId());
	        	pr.setInt(2, countryId);
	        	pr.setString(3, t.getShipmentName());
	        	pr.setInt(4, t.getShipmentType());
	        	   if(pr.executeUpdate()>0)
	        		   System.out.println("id : " + t.getShipmentId() + " name : " + t.getShipmentName());
	        }
	         

	        } catch (SQLException ex) {
	            Logger lgr = Logger.getLogger(AllegroDAO.class.getName());
	            lgr.log(Level.SEVERE, ex.getMessage(), ex);

	        } finally {
	            try {
	                if (rs != null) {
	                    rs.close();
	                }
	                if (st != null) {
	                    st.close();
	                }
	               

	            } catch (SQLException ex) {
	                Logger lgr = Logger.getLogger(AllegroDAO.class.getName());
	                lgr.log(Level.WARNING, ex.getMessage(), ex);
	            }
	        }
		
	}
	
	
	public void addSellAttributes(SellFormType[] value, int categoryId) {
		try{
	    	   PreparedStatement pr = con.prepareStatement("insert into sell_attribute(id ,form_id, title , `values`, values_desc) " +"values( ? ,?, ?, ?, ?);");
	    	   PreparedStatement pr2 = con.prepareStatement("insert into category_sell_attribute(id ,  category_id, sell_attribute_id) " +"values( null , ?, ?);");
	           
	           for(SellFormType data : value){
	        	pr.setInt(1, data.getSellFormParamId());
	        	pr.setInt(2, data.getSellFormId());
	        	pr.setString(3, data.getSellFormTitle());
	        	pr.setString(4, data.getSellFormParamValues());
	        	pr.setString(5, data.getSellFormDesc());
	        	
	        	try{
	        	   if(pr.executeUpdate()>0){
	        		   System.out.println("id : " + data.getSellFormId() );
	        		   pr2.setInt(1, categoryId);
	        		   pr2.setInt(2, data.getSellFormParamId());
	        		   if(pr2.executeUpdate()>0)
	        			   System.out.println("added cat-sell");
	        	   }
	        	 } catch(MySQLIntegrityConstraintViolationException e){
		        	   try {
						//pr2 = con.prepareStatement("insert into category_sell_attribute(id ,  category_id, sell_attribute_id) " +"values( null , ?, ?);");
		        		   pr2.setInt(1, categoryId);
		        		   pr2.setInt(2, data.getSellFormParamId());
		        		   if(pr2.executeUpdate()>0)
		        			   System.out.println("added cat-sell");
		        	   } catch (SQLException w) {
		        		   Logger lgr = Logger.getLogger(AllegroDAO.class.getName());
		        		   lgr.log(Level.SEVERE, w.getMessage(), w);
		        		   return;
					}
	           }
	           }
	           
	         
	          
		           
	        } catch (SQLException ex) {
	            Logger lgr = Logger.getLogger(AllegroDAO.class.getName());
	            lgr.log(Level.SEVERE, ex.getMessage(), ex);

	        } finally {
	            try {
	                if (rs != null) {
	                    rs.close();
	                }
	                if (st != null) {
	                    st.close();
	                }
	               

	            } catch (SQLException ex) {
	                Logger lgr = Logger.getLogger(AllegroDAO.class.getName());
	                lgr.log(Level.WARNING, ex.getMessage(), ex);
	            }
	        }
		
	}

	public ArrayList<Integer> getCategoryIds() {
		try{
			ArrayList<Integer>  list = new ArrayList<Integer>();
	    	    ResultSet rs  = con.createStatement().executeQuery("select id from category where 1=1 and id >=6112 and parent_id !=0 order by id asc");
	           
	        while(rs.next()){
	        	list.add(rs.getInt(1));
	        }
	         
	        return list;
	        } catch (SQLException ex) {
	            Logger lgr = Logger.getLogger(AllegroDAO.class.getName());
	            lgr.log(Level.SEVERE, ex.getMessage(), ex);

	        } finally {
	            try {
	                if (rs != null) {
	                    rs.close();
	                }
	                if (st != null) {
	                    st.close();
	                }
	               

	            } catch (SQLException ex) {
	                Logger lgr = Logger.getLogger(AllegroDAO.class.getName());
	                lgr.log(Level.WARNING, ex.getMessage(), ex);
	            }
	        }
		return null;
	}

	public ArrayList<UrlData> getOfferPhotoUrl() {
		try{
			ArrayList<UrlData>  list = new ArrayList<UrlData>();
	    	    ResultSet rs  = con.createStatement().executeQuery("select id , pic_url from offer where 1=1 order by id asc");
	           
	        while(rs.next()){
	        	list.add(new UrlData(rs.getLong("id"), rs.getString("pic_url")));
	        }
	         
	        return list;
	        } catch (SQLException ex) {
	            Logger lgr = Logger.getLogger(AllegroDAO.class.getName());
	            lgr.log(Level.SEVERE, ex.getMessage(), ex);

	        } finally {
	            try {
	                if (rs != null) {
	                    rs.close();
	                }
	                if (st != null) {
	                    st.close();
	                }
	               

	            } catch (SQLException ex) {
	                Logger lgr = Logger.getLogger(AllegroDAO.class.getName());
	                lgr.log(Level.WARNING, ex.getMessage(), ex);
	            }
	        }
		return null;
	}
	
	public class UrlData{
		Long id;
		String url;
		
		public UrlData(Long i , String u ){
			this.id =i;
			this.url =u;
		}
	}

}
